﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using KVB.Common;
using Customer_Profile.Admin;

namespace Customer_Profile.eCard.Match
{
    public partial class bankaccount_upload : System.Web.UI.Page
    {
        string filename = "";
        DataSet ds = new DataSet();
        string strUserID = "";
        string strUserName = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            Comm.CheckSession(this.Page, "/login.aspx", "usermodel");
            if (Session["usermodel"] != null)
            {
                strUserID = ((UserInfoEntity)Session["usermodel"]).UserLoginId;
                strUserName = ((UserInfoEntity)Session["usermodel"]).UserName;
            }
            else
            {
                Log.WriteDefaultLog("main_01 Page_Load", "Session is null");
                return;
            }
            if (!Page.IsPostBack)
            {
                Comm.BindDll(ref dropBank, "netbank_name", false, "");
                gridBankcflowDetail.Visible = false;
            }
        }

        public string fileupdown()
        {
            if (Path.GetExtension(FileUpload1.FileName) == ".xlsx")
            {
                string filename = FileUpload1.FileName; //FileUpload1.PostedFile.FileName.Substring(FileUpload1.PostedFile.FileName.LastIndexOf("\\"));
                FileUpload1.PostedFile.SaveAs(Server.MapPath("NetBankUpload\\") + filename);//上传文件
                FileUpload1.PostedFile.InputStream.Close();
                lblWarning.Text = "数据上传成功";
                Response.Write("<script>alert('数据上传成功！');</script>");
                return filename;
            }
            else
            {
                //Response.Write("<script language='Javascript'>alert('请选择模板xlsx文件！');</script>");
                lblWarning.Text = "请选择模板xlsx文件！";
                return "";
            }
        }

        public void fillGrid(string filename, string excelName)
        {
            try
            {
                string conn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + Server.MapPath("NetBankUpload\\") + filename + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件  
                string sqlin = "SELECT * FROM [" + excelName + "$]";
                OleDbCommand olecommand = new OleDbCommand(sqlin, new OleDbConnection(conn));
                OleDbDataAdapter adapterin = new OleDbDataAdapter(olecommand);
                //DataSet dsin = new DataSet();
                adapterin.Fill(ds);
                this.gridBankcflowDetail.DataSource = ds.Tables[0];
                this.gridBankcflowDetail.DataBind();
            }
            catch (Exception ex)
            {
                lblWarning.Text = ex.ToString();
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                filename = fileupdown();
                if (!filename.Equals(""))
                {
                    hidFileName.Value = filename;
                    fillGrid(filename, "Sheet1");
                    gridBankcflowDetail.Visible = true;
                }
            }
            catch (Exception ex)
            {
                lblWarning.Text = ex.ToString();
            }
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            fillGrid(hidFileName.Value, "Sheet1");

            SqlConnection conn = null;
            DB.Connection(ref conn);
            SqlTransaction trans = conn.BeginTransaction();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = conn;
            sqlCommand.Transaction = trans;

            try
            {
                //添加NetUplaod Batch
                sqlCommand.Parameters.Clear();
                string c_business_title = txtTitle.Text.Trim();
                string bank_name = dropBank.SelectedItem.Text;

                sqlCommand.Parameters.Add("@business_title", SqlDbType.VarChar, 50);
                sqlCommand.Parameters.Add("@bank_name", SqlDbType.NVarChar, 50);
                sqlCommand.Parameters.Add("@upload_user_id", SqlDbType.NVarChar, 100);
                sqlCommand.Parameters.Add("@upload_user_name", SqlDbType.VarChar, 50);
                sqlCommand.Parameters.Add("@batch_no", SqlDbType.VarChar, 50);

                sqlCommand.Parameters["@business_title"].Value = c_business_title;
                sqlCommand.Parameters["@bank_name"].Value = bank_name;
                sqlCommand.Parameters["@upload_user_id"].Value = strUserID;
                sqlCommand.Parameters["@upload_user_name"].Value = strUserName;
                sqlCommand.Parameters["@batch_no"].Direction = ParameterDirection.Output;

                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "eCard_netbank_batch";
                sqlCommand.ExecuteNonQuery();
                string strBatch_no = sqlCommand.Parameters["@batch_no"].Value.ToString();

                ds.Tables[0].Rows[0].Delete();
                ds.AcceptChanges();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    //添加银行账单信息
                    sqlCommand.Parameters.Clear();                    
                    string business_date = dr[0].ToString();  //strTemp.Substring(0, 4) + "-" + strTemp.Substring(4, 2) + "-" + strTemp.Substring(6, 2);
                    string business_type = ""; //dr[6].ToString();
                    string cparty_bankname = dr[5].ToString();
                    string cparty_accountname = dr[3].ToString();
                    string cparty_accountno = dr[4].ToString();
                    string accounting_key = dr[1].ToString();
                    decimal crebit_amount =decimal.Parse(dr[2].ToString());
                    decimal debit_amount = 0;//decimal.Parse(dr[3].ToString());
                    string summary = dr[6].ToString();
                    string remark = dr[7].ToString();

                    sqlCommand.Parameters.Add("@batch_no", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@bank_name", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@business_date", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@business_type", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@cparty_bankname", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@cparty_accountname", SqlDbType.NVarChar, 250);
                    sqlCommand.Parameters.Add("@cparty_accountno", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@accounting_key", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@crebit_amount", SqlDbType.Decimal);
                    sqlCommand.Parameters.Add("@debit_amount", SqlDbType.Decimal);
                    sqlCommand.Parameters.Add("@summary", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@remark", SqlDbType.VarChar, 250);

                    sqlCommand.Parameters["@batch_no"].Value = strBatch_no;
                    sqlCommand.Parameters["@bank_name"].Value = bank_name;
                    sqlCommand.Parameters["@business_date"].Value = business_date;
                    sqlCommand.Parameters["@business_type"].Value = business_type;
                    sqlCommand.Parameters["@cparty_bankname"].Value = cparty_bankname ;
                    sqlCommand.Parameters["@cparty_accountname"].Value =cparty_accountname ;
                    sqlCommand.Parameters["@cparty_accountno"].Value =cparty_accountno ;
                    sqlCommand.Parameters["@accounting_key"].Value =accounting_key ;
                    sqlCommand.Parameters["@crebit_amount"].Value =crebit_amount ;
                    sqlCommand.Parameters["@debit_amount"].Value =debit_amount ;
                    sqlCommand.Parameters["@summary"].Value = summary;
                    sqlCommand.Parameters["@remark"].Value = remark;


                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = "eCard_netbank_insert";
                    sqlCommand.ExecuteNonQuery();
                }
                trans.Commit();
                Response.Write("<script>alert('导入数据成功！');</script>");
                lblWarning.Text = "导入数据成功";
                hidFileName.Value = "";
            }
            catch (Exception ex)
            {
                lblWarning.Text = "导入数据出错,请联系管理员!"+ex.ToString();
                trans.Rollback();
                Response.Write("<script>alert('导入数据出错！'+" + ex.ToString() + ");</script>");
            }
            finally
            {
                DB.ConnDispose(ref conn);
                trans.Dispose();
                conn.Dispose();
            }
        }
    }
}